package com.example.homework.controller;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

public class Admin {
    private final String dbUrl =
            "jdbc:sqlserver://localhost:1433;database=homework;trustServerCertificate=true";
    private final String userName = "sa";
    private final String userPwd = "bjdjr0544";
    private Connection con;
    private Statement st;
    private ResultSet rs;
    private String sql;

    public ArrayList<Map<String, Object>> adminAllusers() {
        ArrayList<Map<String, Object>> ans = new ArrayList<>();
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            sql = "select * from Buser where Bdel = 0";
            rs = st.executeQuery(sql);
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                map.put("id", rs.getInt("Bid"));
                map.put("name", rs.getString("Bname"));
                map.put("phone", rs.getString("Btele"));
                map.put("sex", rs.getInt("Bsex"));
                map.put("kind", 0);
                map.put("pwd", rs.getString("Bpwd"));
                map.put("addr", rs.getString("Baddr"));
                map.put("status", rs.getString("Bstatus"));
                ans.add(map);
            }
            sql = "select * from Cuser where Cdel = 0";
            rs = st.executeQuery(sql);
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                map.put("id", rs.getInt("Cid"));
                map.put("name", rs.getString("Cname"));
                map.put("phone", rs.getString("Ctele"));
                map.put("sex", rs.getInt("Csex"));
                map.put("kind", 1);
                map.put("pwd", rs.getString("Cpwd"));
                map.put("addr", rs.getString("Caddr"));
                map.put("status", rs.getString("Cstatus"));
                ans.add(map);
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        return ans;
    }

    public Map<String, Object> adminDeluser(int id) {
        int state = 0;
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            sql = "select * from Cuser where Cid = " + id + " and Cdel = 0";
            rs = st.executeQuery(sql);
            int flag = 0;
            if (rs.next()) {
                sql = "update Cuser set Cdel = 1 where Cid = " + id;
                st.executeUpdate(sql);
            } else {
                sql = "select * from Buser where Bid = " + id + " and Bdel = 0";
                rs = st.executeQuery(sql);
                if (rs.next()) {
                    sql = "update Buser set Bdel = 1 where Bid = " + id;
                    st.executeUpdate(sql);
                } else {
                    flag = 1;
                }
            }
            if (flag == 0) {
                sql = "insert into Record(Rtype, Rtime, Rid) values (11, getdate(), " + id + ")";
                st.executeUpdate(sql);
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        Map<String, Object> map = new HashMap<>();
        map.put("state", state);
        return map;
    }

    public Map<String, Object> adminUserpass(int id) {
        int state = 0;
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            sql = "select * from Cuser where Cid = " + id + " and Cstatus = N'未审核'";
            rs = st.executeQuery(sql);
            int flag = 0;
            if (rs.next()) {
                sql = "update Cuser set Cstatus = N'已审核' where Cid = " + id;
                st.executeUpdate(sql);
            } else {
                sql = "select * from Buser where Bid = " + id + " and Bstatus = N'未审核'";
                rs = st.executeQuery(sql);
                if (rs.next()) {
                    sql = "update Buser set Bstatus = N'已审核' where Bid = " + id;
                    st.executeUpdate(sql);
                } else {
                    flag = 1;
                }
            }
            if (flag == 0) {
                sql = "insert into Record(Rtype, Rtime, Rid) values (12, getdate(), " + id + ")";
                st.executeUpdate(sql);
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        Map<String, Object> map = new HashMap<>();
        map.put("state", state);
        return map;
    }

    public ArrayList<Map<String, Object>> adminAllfoods() {
        ArrayList<Map<String, Object>> ans = new ArrayList<>();
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            sql = "select * from Food where Fdel = 0";
            rs = st.executeQuery(sql);
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                map.put("id", rs.getInt("Fid"));
                map.put("food", rs.getString("Fname"));
                map.put("price", String.valueOf(rs.getFloat("Fprice")));
                map.put("stock", rs.getInt("Fstock"));
                map.put("type", rs.getString("Ftype"));
                map.put("status", rs.getString("Fstatus"));
                map.put("seller_id", rs.getInt("Bid"));
                sql = "select Bname from Buser where Bid = " + rs.getInt("Bid");
                ResultSet temp = st.executeQuery(sql);
                String shop = "";
                if (temp.next()) {
                    shop = temp.getString("Bname");
                }
                map.put("seller_name", shop);
                map.put("status_pass", rs.getString("Fpass"));
                ans.add(map);
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        return ans;
    }

    public Map<String, Object> adminFoodpass(int id) {
        int state = 0;
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            sql = "select * from Food where Fid = " + id + " and Fpass = N'未审核'";
            rs = st.executeQuery(sql);
            if (rs.next()) {
                sql = "update Food set Fpass = N'已审核' where Fid = " + id;
                st.executeUpdate(sql);
                sql = "insert into Record(Rtype, Rtime, Rid, Fid) values " +
                        "(13, getdate(), 0, " + id + ")";
                st.executeUpdate(sql);
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        Map<String, Object> map = new HashMap<>();
        map.put("state", state);
        return map;
    }

    public Map<String, Object> adminDelfood(int fid, int bid) {
        int state = 0;
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            sql = "select Fid from Food where Fid = " + fid + " and Bid = " + bid + " and Fdel = 0";
            rs = st.executeQuery(sql);
            if (rs.next()) {
                sql = "update Food set Fdel = 1 where Fid = " + fid;
                st.executeUpdate(sql);
                sql = "insert into Record(Rtype, Rtime, Rid, Fid) values " +
                        "(14, getdate(), 0, " + fid + ")";
                st.executeUpdate(sql);
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        Map<String, Object> map = new HashMap<>();
        map.put("state", state);
        return map;
    }

    public ArrayList<Map<String, Object>> adminAllorders() {
        ArrayList<Map<String, Object>> ans = new ArrayList<>();
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            sql = "select * from Order_shop where Odel = 0";
            rs = st.executeQuery(sql);
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                map.put("id", rs.getInt("Oid"));
                map.put("phone", rs.getString("Ctele"));
                map.put("time", rs.getDate("Otime") + " " + rs.getTime("Otime"));
                map.put("value", String.valueOf(rs.getFloat("Oval")));
                map.put("status", rs.getString("Ostatus"));
                map.put("tip", rs.getString("Otip"));
                String temp = "select Bname from Buser where Bid = " + rs.getInt("Bid");
                ResultSet tempRs = st.executeQuery(temp);
                if (tempRs.next()) {
                    map.put("shop", tempRs.getString("Bname"));
                } else {
                    map.put("shop", "None");
                }
                temp = "select Cname from Cuser where Cid = " + rs.getInt("Cid");
                tempRs = st.executeQuery(temp);
                if (tempRs.next()) {
                    map.put("cust", tempRs.getString("Cname"));
                } else {
                    map.put("cust", "None");
                }
                temp = "select * from Order_food where Oid = " + rs.getInt("Oid");
                tempRs = st.executeQuery(temp);
                ArrayList<Map<String, Object>> foods = new ArrayList<>();
                while (tempRs.next()) {
                    Map<String, Object> subMap = new HashMap<>();
                    subMap.put("food", tempRs.getString("Fname"));
                    subMap.put("num", tempRs.getInt("Fnum"));
                    foods.add(subMap);
                }
                map.put("foods", foods);
                ans.add(map);
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        return ans;
    }

    public Map<String, Object> adminOrderpass(int id) {
        int state = 0;
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            sql = "select Oid from Order_shop where Oid = " + id + " and Ostatus = N'未通过'";
            rs = st.executeQuery(sql);
            if (rs.next()) {
                sql = "update Order_shop set Ostatus = N'未接单' where Oid = " + id;
                st.executeUpdate(sql);
                sql = "insert into Record(Rtype, Rtime, Rid, Oid) values " +
                        "(15, getdate(), 0, " + id + ")";
                st.executeUpdate(sql);
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        Map<String, Object> map = new HashMap<>();
        map.put("state", state);
        return map;
    }

    public Map<String, Object> adminDelorder(int id) {
        int state = 0;
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            sql = "select Oid from Order_shop where Oid = " + id + " and Odel = 0";
            rs = st.executeQuery(sql);
            if (rs.next()) {
                sql = "update Order_shop set Odel = 1 where Oid = " + id;
                st.executeUpdate(sql);
                sql = "insert into Record(Rtype, Rtime, Rid, Oid) values " +
                        "(16, getdate(), 0, " + id + ")";
                st.executeUpdate(sql);
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        Map<String, Object> map = new HashMap<>();
        map.put("state", state);
        return map;
    }
}
